using System;
using System.Collections.Generic;
using System.Text;
using Customs.Components.KDT.GC.Info;
using Customs.Components;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
namespace Customs.Components.KDT.GC
{
    public class HopDong : HopDongInfo
    {
        public void Load(HopDongInfo entityInfo)
        {
            this._SoTiepNhan = entityInfo.SoTiepNhan;
            this._MaHaiQuan = entityInfo.MaHaiQuan;
            this._NamTiepNhan = entityInfo.NamTiepNhan;
            this.NgayTiepNhan = entityInfo.NgayTiepNhan;
            this._SoHopDong = entityInfo.SoHopDong;
            this._MaDoanhNghiep = entityInfo.MaDoanhNghiep;
            this._NgayKy = entityInfo.NgayKy;
            this._NgayDangKy = entityInfo.NgayDangKy;
            this._NgayGiaHan = entityInfo.NgayGiaHan;
            this._NgayHetHan = entityInfo.NgayHetHan;
            this._TriGiaGiaCong = entityInfo.TriGiaGiaCong;
            this._NuocThue_ID = entityInfo.NuocThue_ID;
            this._NguyenTe_ID = entityInfo.NguyenTe_ID;
            this._TrangThaiXL = entityInfo.TrangThaiXL;
            this._MaDaiLy = entityInfo.MaDaiLy;
            this.ThietBiCollection = entityInfo.ThietBiCollection;
            this.SanPhamCollection = entityInfo.SanPhamCollection;
            this.NhomSanPhamGiaCong = entityInfo.NhomSanPhamGiaCong;
            this.NguyenPhuLieuCollection = entityInfo.NguyenPhuLieuCollection;
        }

        public bool Load()
        {
            SqlDataReader reader = this.Select();
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("SoTiepNhan"))) this.SoTiepNhan = reader.GetInt64(reader.GetOrdinal("SoTiepNhan"));
                if (!reader.IsDBNull(reader.GetOrdinal("So_HD"))) this.SoHopDong = reader.GetString(reader.GetOrdinal("So_HD"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) this.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) this.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("Nam_TN"))) this.NamTiepNhan = reader.GetInt16(reader.GetOrdinal("Nam_TN"));

                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_Ky"))) this.NgayKy = reader.GetDateTime(reader.GetOrdinal("Ngay_Ky"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_DK"))) this.NgayDangKy = reader.GetDateTime(reader.GetOrdinal("Ngay_DK"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_HH"))) this.NgayHetHan = reader.GetDateTime(reader.GetOrdinal("Ngay_HH"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_GH"))) this.NgayGiaHan = reader.GetDateTime(reader.GetOrdinal("Ngay_GH"));
                if (!reader.IsDBNull(reader.GetOrdinal("NuocThueGC"))) this.NuocThue_ID = reader.GetString(reader.GetOrdinal("NuocThueGC"));
                if (!reader.IsDBNull(reader.GetOrdinal("TriGiaGC"))) this.TriGiaGiaCong = reader.GetDecimal(reader.GetOrdinal("TriGiaGC"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgTe"))) this.NguyenTe_ID = reader.GetString(reader.GetOrdinal("NgTe"));
                if (!reader.IsDBNull(reader.GetOrdinal("TrangThaiXL"))) this.TrangThaiXL = reader.GetInt32(reader.GetOrdinal("TrangThaiXL"));
                reader.Close();
                this.NhomSanPhamGiaCong = NhomSanPham.selectNhomSanPhamInfoCollectionByHopDong(this.MaHaiQuan, this.MaDoanhNghiep, this.SoHopDong, this.NgayKy);
                this.SanPhamCollection = SanPham.selectSanPhamInfoCollectionByHopDong(this.MaHaiQuan, this.MaDoanhNghiep, this.SoHopDong, this.NgayKy);
                this.NguyenPhuLieuCollection = NguyenPhuLieu.selectNguyenPhuLieuInfoCollectionByHopDong(this.MaHaiQuan, this.MaDoanhNghiep, this.SoHopDong, this.NgayKy);
                this.ThietBiCollection = ThietBi.selectThietBiInfoCollectionByHopDong(this.MaHaiQuan, this.MaDoanhNghiep, this.SoHopDong, this.NgayKy);
                return true;
            }
            reader.Close();
            return false;
        }

        //dung xoa
        public static HopDongInfo RequestStatus(long SoTiepNhan, string MaHaiQuan, short NamTiepNhan)
        {
            HopDongInfo hd = new HopDongInfo();
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_TNDKDT AS [SoTiepNhan], ");
            query.Append("Ma_HQTN AS [MaHaiQuan], ");
            query.Append("Nam_TN AS [Nam_TN], ");
            query.Append("So_HD AS [So_HD], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [Ngay_Ky], ");
            query.Append("Ngay_DK AS [Ngay_DK], ");
            query.Append("Ngay_HH AS [Ngay_HH], ");
            query.Append("Ngay_GH AS [Ngay_GH], ");
            query.Append("NuocThueGC AS [NuocThueGC], ");
            query.Append("TriGiaGC AS [TriGiaGC], ");
            query.Append("NgTe AS [NgTe], ");
            query.Append("TrangThaiXL AS [TrangThaiXL] ");
            query.Append("FROM dbo.[tblDHDGC] ");
            query.Append("WHERE ");
            query.Append("So_TNDKDT = @So_TNDKDT AND Ma_HQTN = @Ma_HQTN AND Nam_TN = @Nam_TN");

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = SoTiepNhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char,6);
            parameters[1].Value = MaHaiQuan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = NamTiepNhan;          

            SqlDataReader reader=SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
      
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("SoTiepNhan"))) hd.SoTiepNhan = reader.GetInt64(reader.GetOrdinal("SoTiepNhan"));
                if (!reader.IsDBNull(reader.GetOrdinal("So_HD"))) hd.SoHopDong = reader.GetString(reader.GetOrdinal("So_HD"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) hd.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) hd.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("Nam_TN"))) hd.NamTiepNhan = reader.GetInt16(reader.GetOrdinal("Nam_TN"));

                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_Ky"))) hd.NgayKy = reader.GetDateTime(reader.GetOrdinal("Ngay_Ky"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_DK"))) hd.NgayDangKy = reader.GetDateTime(reader.GetOrdinal("Ngay_DK"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_HH"))) hd.NgayHetHan = reader.GetDateTime(reader.GetOrdinal("Ngay_HH"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_GH"))) hd.NgayGiaHan = reader.GetDateTime(reader.GetOrdinal("Ngay_GH"));
                if (!reader.IsDBNull(reader.GetOrdinal("NuocThueGC"))) hd.NuocThue_ID = reader.GetString(reader.GetOrdinal("NuocThueGC"));
                if (!reader.IsDBNull(reader.GetOrdinal("TriGiaGC"))) hd.TriGiaGiaCong = reader.GetDecimal(reader.GetOrdinal("TriGiaGC"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgTe"))) hd.NguyenTe_ID = reader.GetString(reader.GetOrdinal("NgTe"));
                if (!reader.IsDBNull(reader.GetOrdinal("TrangThaiXL"))) hd.TrangThaiXL = reader.GetInt32(reader.GetOrdinal("TrangThaiXL"));
                reader.Close();
                hd.NhomSanPhamGiaCong = NhomSanPham.selectNhomSanPhamInfoCollectionByHopDong(hd.MaHaiQuan, hd.MaDoanhNghiep, hd.SoHopDong, hd.NgayKy);
                hd.SanPhamCollection = SanPham.selectSanPhamInfoCollectionByHopDong(hd.MaHaiQuan, hd.MaDoanhNghiep, hd.SoHopDong, hd.NgayKy);
                hd.NguyenPhuLieuCollection = NguyenPhuLieu.selectNguyenPhuLieuInfoCollectionByHopDong(hd.MaHaiQuan, hd.MaDoanhNghiep, hd.SoHopDong, hd.NgayKy);
                hd.ThietBiCollection = ThietBi.selectThietBiInfoCollectionByHopDong(hd.MaHaiQuan, hd.MaDoanhNghiep, hd.SoHopDong, hd.NgayKy);
              
                return hd;
            }
            reader.Close();
            return null;
        }
        public static HopDongInfo DownLoad(long SoTiepNhan, string MaHaiQuan, short NamTiepNhan)
        {
            HopDongInfo hd = new HopDongInfo();
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_TNDKDT AS [SoTiepNhan], ");
            query.Append("Ma_HQTN AS [MaHaiQuan], ");
            query.Append("Nam_TN AS [Nam_TN], ");
            query.Append("So_HD AS [So_HD], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [Ngay_Ky], ");
            query.Append("Ngay_DK AS [Ngay_DK], ");
            query.Append("Ngay_HH AS [Ngay_HH], ");
            query.Append("Ngay_GH AS [Ngay_GH], ");
            query.Append("NuocThueGC AS [NuocThueGC], ");
            query.Append("TriGiaGC AS [TriGiaGC], ");
            query.Append("NgTe AS [NgTe], ");
            query.Append("TrangThaiXL AS [TrangThaiXL] ");
            query.Append("FROM dbo.[tblDHDGC] ");
            query.Append("WHERE ");
            query.Append("So_TNDKDT = @So_TNDKDT AND Ma_HQTN = @Ma_HQTN AND Nam_TN = @Nam_TN");

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = SoTiepNhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char, 6);
            parameters[1].Value = MaHaiQuan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = NamTiepNhan;

            SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);

            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("SoTiepNhan"))) hd.SoTiepNhan = reader.GetInt64(reader.GetOrdinal("SoTiepNhan"));
                if (!reader.IsDBNull(reader.GetOrdinal("So_HD"))) hd.SoHopDong = reader.GetString(reader.GetOrdinal("So_HD"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) hd.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) hd.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("Nam_TN"))) hd.NamTiepNhan = reader.GetInt16(reader.GetOrdinal("Nam_TN"));

                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_Ky"))) hd.NgayKy = reader.GetDateTime(reader.GetOrdinal("Ngay_Ky"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_DK"))) hd.NgayDangKy = reader.GetDateTime(reader.GetOrdinal("Ngay_DK"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_HH"))) hd.NgayHetHan = reader.GetDateTime(reader.GetOrdinal("Ngay_HH"));
                if (!reader.IsDBNull(reader.GetOrdinal("Ngay_GH"))) hd.NgayGiaHan = reader.GetDateTime(reader.GetOrdinal("Ngay_GH"));
                if (!reader.IsDBNull(reader.GetOrdinal("NuocThueGC"))) hd.NuocThue_ID = reader.GetString(reader.GetOrdinal("NuocThueGC"));
                if (!reader.IsDBNull(reader.GetOrdinal("TriGiaGC"))) hd.TriGiaGiaCong = reader.GetDecimal(reader.GetOrdinal("TriGiaGC"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgTe"))) hd.NguyenTe_ID = reader.GetString(reader.GetOrdinal("NgTe"));
                if (!reader.IsDBNull(reader.GetOrdinal("TrangThaiXL"))) hd.TrangThaiXL = reader.GetInt32(reader.GetOrdinal("TrangThaiXL"));

                hd.NhomSanPhamGiaCong = NhomSanPham.selectNhomSanPhamInfoCollectionByHopDong(hd.MaHaiQuan, hd.MaDoanhNghiep, hd.SoHopDong, hd.NgayKy);
                hd.SanPhamCollection = SanPham.selectSanPhamInfoCollectionByHopDong(hd.MaHaiQuan, hd.MaDoanhNghiep, hd.SoHopDong, hd.NgayKy);
                hd.NguyenPhuLieuCollection = NguyenPhuLieu.selectNguyenPhuLieuInfoCollectionByHopDong(hd.MaHaiQuan, hd.MaDoanhNghiep, hd.SoHopDong, hd.NgayKy);
                hd.ThietBiCollection = ThietBi.selectThietBiInfoCollectionByHopDong(hd.MaHaiQuan, hd.MaDoanhNghiep, hd.SoHopDong, hd.NgayKy);
                reader.Close();
                return hd;
            }
            reader.Close();
            return null;
        }
        
        public static HopDongInfoCollection RequestStatus(HopDongInfoCollection collection)
        {
            HopDongInfoCollection col = new HopDongInfoCollection();
            foreach (HopDongInfo hd in collection)
            {
                HopDongInfo requestHd = RequestStatus(hd.SoTiepNhan, hd.MaHaiQuan, (short)hd.NamTiepNhan);
                col.Add(requestHd);
            }
            return col;
        }
      
        private long getSoMax(SqlTransaction transaction)
        {
            string sqlSelect = "SELECT SoMax FROM [tblCAPSO_HDGC] WHERE ([Ma_HQTN] = @MaHaiQuan) AND ([Nam_TN] = @NamTiepNhan)";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._MaHaiQuan;

            parameters[1] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = this._NamTiepNhan;

            SqlDataReader reader = SqlHelper.ExecuteReader(transaction, CommandType.Text, sqlSelect, parameters);
            long result = 0;
            if (reader.Read())
            {
                result = Int64.Parse(reader["SoMax"].ToString());
            }
            reader.Close();
            return result;
        }

        private int updateSoMax(SqlTransaction transaction)
        {
            string sqlUpdate = "UPDATE [tblCAPSO_HDGC] SET [SoMax] = [SoMax] + 1 WHERE ([Ma_HQTN] = @MaHaiQuan) AND ([Nam_TN] = @NamTiepNhan)";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._MaHaiQuan;

            parameters[1] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = this._NamTiepNhan;

            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlUpdate, parameters);
        }

        private int insertSoMax(SqlTransaction transaction)
        {
            string sqlInsert = "INSERT INTO [tblCAPSO_HDGC] ([Ma_HQTN],	[Nam_TN], [SoMax]) VALUES (@MaHaiQuan, @NamTiepNhan, 1)";

            SqlParameter[] parameters = new SqlParameter[2];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Direction = ParameterDirection.Input;
            parameters[0].Value = this._MaHaiQuan;

            parameters[1] = new SqlParameter("@NamTiepNhan", SqlDbType.SmallInt);
            parameters[1].Direction = ParameterDirection.Input;
            parameters[1].Value = this._NamTiepNhan;

            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, sqlInsert, parameters);
        }

        //-----------------------------------------------------------------------------------------
        public long GetSoTiepNhan(SqlTransaction transaction)
        {
            long somax = this.getSoMax(transaction);
            if (somax == 0)
            {
                this.insertSoMax(transaction);
                somax = 1;
            }
            else
            {
                this.updateSoMax(transaction);
                somax = somax + 1;
            }
            return somax;
        }

        public SqlDataReader Select()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_TNDKDT AS [SoTiepNhan], ");
            query.Append("Ma_HQTN AS [MaHaiQuan], ");
            query.Append("Nam_TN AS [Nam_TN], ");
            query.Append("So_HD AS [So_HD], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [Ngay_Ky], ");
            query.Append("Ngay_DK AS [Ngay_DK], ");
            query.Append("Ngay_HH AS [Ngay_HH], ");
            query.Append("Ngay_GH AS [Ngay_GH], ");
            query.Append("NuocThueGC AS [NuocThueGC], ");
            query.Append("TriGiaGC AS [TriGiaGC], ");
            query.Append("NgTe AS [NgTe], ");
            query.Append("TrangThaiXL AS [TrangThaiXL] ");
            query.Append("FROM dbo.[tblDHDGC] ");
            query.Append("WHERE ");
            query.Append("So_TNDKDT = @So_TNDKDT AND Ma_HQTN = @Ma_HQTN AND Nam_TN = @Nam_TN");

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = this.SoTiepNhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char, 6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = this.NamTiepNhan;

            return SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
        }
        public int Delete(SqlTransaction transaction)
        {
            string query = string.Format("DELETE FROM tblDHDGC WHERE So_TNDKDT = {0} AND Ma_HQTN = '{1}' AND Nam_TN = {2} ", this.SoTiepNhan, this.MaHaiQuan, this.NamTiepNhan);      
            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query);
        }
        public int Delete()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            string query = string.Format("DELETE FROM tblDHDGC WHERE So_HD = '{0}' AND Ma_HQHD = '{1}' AND DVGC = '{2}' and ngay_ky={3}", this.SoHopDong, this.MaHaiQuan, this.MaDoanhNghiep, this.NgayKy);
            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, query);
        }
        //---------------------------------------------------------------------------------------------
        public void Delete(SqlTransaction transaction, HopDongInfoCollection collection)
        {
            HopDong hd = new HopDong();
            foreach (HopDongInfo itemInfo in collection)
            {
                hd.Load(itemInfo);
                hd.Delete();
            }
        }
        //---------------------------------------------------------------------------------------------
        public bool Delete(HopDongInfoCollection collection)
        {
            bool returnValue;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();

                try
                {
                    this.Delete(transaction, collection);
                    transaction.Commit();
                    returnValue = true;
                }
                catch
                {
                    returnValue = false;
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }
            return returnValue;
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction, HopDongInfoCollection collection)
        {
            HopDong hd = new HopDong();
            foreach (HopDongInfo itemInfo in collection)
            {
                hd.Load(itemInfo);
                hd.InsertTransaction(transaction);
            }
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction)
        {
            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO tblDHDGC ( ");
            query.Append("[So_TNDKDT] , ");
            query.Append("[Ma_HQTN] , ");
            query.Append("[Nam_TN] , ");
            query.Append("[So_HD] , ");
            query.Append("[Ma_HQHD] , ");
            query.Append("[DVGC] , ");
            query.Append("[Ten_DV] , ");
            query.Append("[Ngay_Ky] , ");
            query.Append("[Ngay_DK] , ");
            query.Append("[Ngay_HH] , ");            
            query.Append("[NuocThueGC] , ");
            query.Append("[TriGiaGC] , ");
            query.Append("[NgTe] , ");
            query.Append("[Ngay_VM] , ");
            query.Append("[TrangThaiXL] , ");
            query.Append("[KieuKB] , ");
            query.Append("[Ma_DVKB])  ");   
       
            query.Append("VALUES ( ");

            query.Append("@So_TNDKDT, ");
            query.Append("@Ma_HQTN, ");
            query.Append("@Nam_TN, ");
            query.Append("@So_HD, ");
            query.Append("@Ma_HQHD, ");
            query.Append("@DVGC, ");
            query.Append("@Ten_DV, ");
            query.Append("@Ngay_Ky, ");
            query.Append("@Ngay_DK , ");
            query.Append("@Ngay_HH , ");            
            query.Append("@NuocThueGC , ");
            query.Append("@TriGiaGC , ");
            query.Append("@NgTe , ");
            query.Append("@Ngay_VM , ");
            query.Append("@TrangThaiXL , ");
            query.Append("@KieuKB , ");
            query.Append("@Ma_DVKB)  ");
            this.NamTiepNhan = DateTime.Now.Year;
            this.SoTiepNhan = GetSoTiepNhan(transaction);           
            SqlParameter[] parameters = new SqlParameter[17];

            parameters[0] = new SqlParameter("@So_TNDKDT", SqlDbType.BigInt);
            parameters[0].Value = this.SoTiepNhan;

            parameters[1] = new SqlParameter("@Ma_HQTN", SqlDbType.Char,6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@Nam_TN", SqlDbType.SmallInt);
            parameters[2].Value = this.NamTiepNhan;

            parameters[3] = new SqlParameter("@So_HD", SqlDbType.VarChar);
            parameters[3].Value = this.SoHopDong;

            parameters[4] = new SqlParameter("@Ma_HQHD", SqlDbType.VarChar);
            parameters[4].Value = this.MaHaiQuan;

            parameters[5] = new SqlParameter("@DVGC", SqlDbType.VarChar);
            parameters[5].Value = this.MaDoanhNghiep;

            parameters[6] = new SqlParameter("@Ten_DV", SqlDbType.VarChar);
            parameters[6].Value = null;

            parameters[7] = new SqlParameter("@Ngay_Ky", SqlDbType.DateTime);
            parameters[7].Value = this.NgayKy;

            parameters[8] = new SqlParameter("@Ngay_DK", SqlDbType.DateTime);
            parameters[8].Value = this.NgayDangKy;

            parameters[9] = new SqlParameter("@Ngay_HH", SqlDbType.DateTime);
            parameters[9].Value = this.NgayHetHan;
          

            parameters[10] = new SqlParameter("@NuocThueGC", SqlDbType.NVarChar);
            parameters[10].Value = this.NuocThue_ID;
            this.TriGiaGiaCong = 1000;
            parameters[11]= new SqlParameter("@TriGiaGC", SqlDbType.Decimal);
            parameters[11].Value = this.TriGiaGiaCong;

            parameters[12] = new SqlParameter("@NgTe", SqlDbType.Char,3);
            parameters[12].Value = this.NguyenTe_ID;

            parameters[13] = new SqlParameter("@Ngay_VM", SqlDbType.DateTime);
            parameters[13].Value = DateTime.Today;

            parameters[14] = new SqlParameter("@TrangThaiXL", SqlDbType.Int);
            parameters[14].Value = 0;

            parameters[15] = new SqlParameter("@KieuKB", SqlDbType.Int);
            parameters[15].Value = 1;

            parameters[16] = new SqlParameter("@Ma_DVKB", SqlDbType.Char,14);
            parameters[16].Value = this.MaDoanhNghiep;

            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query.ToString(), parameters);
        }
        public long KhaiBaoHopDong(HopDongInfo entity)
        {            
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();
                try
                {                   
                    this.Load(entity);                  
                    if (SoTiepNhan > 0)
                    {
                        this.Load();
                        if (this.TrangThaiXL == 1)
                            throw new Exception("1");
                        DeleteHopDong(transaction);
                        this.Load(entity);
                    }                    
                    InsertFull(transaction);
                    transaction.Commit();                    
                }
                catch(Exception ex)
                {                  
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return this.SoTiepNhan;
        }
        private void KhaiBaoHopDong(HopDongInfo entity, SqlTransaction transaction)
        {
           this.Load(entity);
            if (SoTiepNhan > 0)
            {
                this.Load();
                if (this.TrangThaiXL == 1)
                    throw new Exception("1");
                DeleteHopDong(transaction);
                this.Load(entity);
            }
            InsertFull(transaction);
            entity.SoTiepNhan = this.SoTiepNhan;
        }
        public void  KhaiBaoHopDong(HopDongInfoCollection collection)
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    foreach (HopDongInfo entity in collection)
                    {
                        this.KhaiBaoHopDong(entity, transaction);
                    }
                    transaction.Commit();
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }            
        }
        public void InsertFull(SqlTransaction transaction)
        {
            InsertTransaction(transaction);            
            if(this.NhomSanPhamGiaCong!=null)
                foreach (NhomSanPhamInfo item in this.NhomSanPhamGiaCong)
                {
                    NhomSanPham nhomsp = new NhomSanPham();
                    nhomsp.Load(item);
                    nhomsp.InsertTransaction(transaction);
                }
            if(this.NguyenPhuLieuCollection!=null)
                foreach (NguyenPhuLieuInfo item in this.NguyenPhuLieuCollection)
                {
                    NguyenPhuLieu npl = new NguyenPhuLieu();
                    npl.Load(item);
                    npl.InsertTransaction(transaction);
                }
            if(this.SanPhamCollection!=null)
                foreach (SanPhamInfo item in this.SanPhamCollection)
                {
                    SanPham sp = new SanPham();
                    sp.Load(item);
                    sp.InsertTransaction(transaction);
                }
            if(this.ThietBiCollection!=null)
                foreach (ThietBiInfo item in this.ThietBiCollection)
                {
                    ThietBi tb = new ThietBi();
                    tb.Load(item);
                    tb.InsertTransaction(transaction);
                }                            
        }
        public bool HuyHopDong()
        {
            bool returnValue;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();

                try
                {
                    this.Load();
                    DeleteHopDong(transaction);
                    transaction.Commit();
                    returnValue = true;
                }
                catch(Exception ex)
                {                   
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
            return returnValue;
        }
        public void DeleteHopDong(SqlTransaction transaction)
        {
                Delete(transaction);
                if(this.NhomSanPhamGiaCong!=null)
                    foreach (NhomSanPhamInfo item in this.NhomSanPhamGiaCong)
                    {
                        NhomSanPham nhomsp = new NhomSanPham();
                        nhomsp.Load(item);
                        nhomsp.Delete(transaction);
                    }
                if (this.NguyenPhuLieuCollection != null)
                    foreach (NguyenPhuLieuInfo item in this.NguyenPhuLieuCollection)
                    {
                        NguyenPhuLieu npl = new NguyenPhuLieu();
                        npl.Load(item);
                        npl.Delete(transaction);
                    }
                if(this.SanPhamCollection!=null)
                    foreach (SanPhamInfo item in this.SanPhamCollection)
                    {
                        SanPham sp = new SanPham();
                        sp.Load(item);
                        sp.Delete(transaction);
                    }
                if(this.ThietBiCollection!=null)
                    foreach (ThietBiInfo item in this.ThietBiCollection)
                    {
                        ThietBi tb = new ThietBi();
                        tb.Load(item);
                        tb.Delete(transaction);
                    }
            
        }

        //-------------------------------------------------------------------------------------------------
        public static void HuyHopDong(HopDongInfoCollection collection)
        {
            bool returnValue;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();

                try
                {
                    foreach (HopDongInfo entity in collection)
                    {
                        HopDong hd = new HopDong();
                        hd.Load(entity);
                        hd.HuyHopDong(transaction);
                    }
                    transaction.Commit();                    
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }            
        }


        private void HuyHopDong(SqlTransaction transaction)
        {
            this.Load();
            DeleteHopDong(transaction);
            transaction.Commit();                          
        }
    }
}
